* 發生原因
listagg在官方文件的應用是
As a group-set aggregate, the function operates on and returns an output row for each group defined by the GROUP BY clause.
目的在於使用某個GROUP BY條件,將某欄位的值串接起來
但是這種聚合函數(Aggregate function)最多只能支援4000 Bytes(Varchar2)
若超過4000 Bytes,就會跳出ORA-01489
ORA-01489 result of string concatenation is too long
* 解決方式
1. 升級到Oracle 12
根據Stackflow上的說法,升級到Oracle 12可以設定函數return的長度最高到3200 byte
2. 使用XMLAGG
這篇主要想提到的方法,語法如下:
SELECT dbms_xmlgen.convert(RTRIM(extract(XMLAGG(XMLELEMENT(e, colA, '<') ORDER BY colA, colB), '//text()').getclobval() , ','), 1)
FROM tableA
目的在於回傳一個CLOB型態的字串
語法說明:
注意
資料顯示的部份想表達的是encode後的編碼,但是直接使用會顯示符號,所以每一個字元用空白隔開
XMLELEMENT
● 功能:將非XML格式的資料轉成XML,若有特殊字元,則會自動encode
● 格式:XMLELEMENT("TAG名稱", 欄位, 連接字元)
● 範例:XMLELEMENT("SUBCON", subcon_code, '<')
● 資料:<SUBCON>A & l t ;</SUBCON>
XMLAGG
● 功能:將XML格式的資料聚合成XML格式的文件
● 格式:XMLAGG(XML格式的資料 [ORDER BY語法])
● 範例:XMLAGG(XMLELEMENT("SUBCON", subcon_code, '<') ORDER BY subcon_code)
● 資料:<SUBCON>B & l t ;</SUBCON><SUBCON>C & l t ;</SUBCON><SUBCON>A & l t ;</SUBCON>
EXTRACT
● 功能:從XML格式的資料中擷取想要的部份
● 格式:EXTRACT(XML格式的資料, XPath [, Namespace_str])
● 範例:EXTRACT(XMLAGG(XMLELEMENT("SUBCON", subcon_code, '<') ORDER BY subcon_code), '//text()')
● 資料:B & l t ; C & l t ; A & l t ;
● 備註:'//text()'指的是取出所有第一層Tag下的文字,「//」表示所有
GETCLOBVAL()
● 功能:從XML格式的資料中取得排序後的資料
● 範例:EXTRACT(XMLAGG(XMLELEMENT("SUBCON", subcon_code, '<') ORDER BY subcon_code), '//text()').GETCLOBVAL()
● 資料:A & l t ; B & l t ; C
dbms_xmlgen.convert
● 功能:(※若GETCLOBVAL()產生的字串會有"encode過的字串",才需要使用這個function)
在dbms_xmlgen Package下的功能都跟轉換成XML格式有關。
convert目的在於轉換成XML格式時,保留特殊字元,不將特殊字元重新編碼過。例如:將「& l t ;」轉換成「<」
● 格式:dbms_xmlgen.convert(XML格式的資料 [, 0或1])
-- 1表示decode
-- 0表示encode
● 範例:dbms_xmlgen.convert(EXTRACT(XMLAGG(XMLELEMENT("SUBCON", subcon_code, '<') ORDER BY subcon_code), '//text()').GETCLOBVAL(), 1)
● 資料:A< B< C<
* Reference
https://stackoverflow.com/questions/11744465/xpath-difference-between-node-and-text